<?php

/**
 * 合同仓库
 * @author wangwx 2015-5-7
 */

namespace common\repositories\myhtsq;

use common\entities\myhtsq\ContractApplicationEntity;
use common\entities\myhtsq\ContractCaibaoDetailEntity;
use common\entities\myhtsq\ContractPayinfoEntity;
use common\entities\myhtsq\ContractPayoverEntity;
use common\entities\myhtsq\ContractShipmentEntity;
use common\entities\myhtsq\ErpUserEntity;
use common\entities\myhtsq\InvoiceEntity;
use common\entities\myhtsq\SpecialApprovalAuthorizeEntity;
use common\repositories\BaseRepository;
use common\entities\myhtsq\ContractManageEntity;
use common\entities\myhtsq\ContractTenantRelationEntity;
use common\models\myhtsq\ContractManageModel;
use common\services\myhtsq\ContractService;
use common\services\myhtsq\OutStorageService;
use common\services\myhtsq\ErpInterfaceService;
use common\support\StringHelper;
use common\support\Helper;
use yii\base\Exception;
use yii\db\ActiveQuery;
use yii\db\Connection;
use yii\db\Query;
use common\entities\ConfigEntity;

class ContractManageRepository extends BaseRepository
{

    /**
     * 构造方法
     * @param unknown $config (optional)
     */
    public function __construct($config = [])
    {
        parent::__construct($config);
    }

    /**
     * 保存合同
     * @param $contractModel 合同模型
     * @param bool $isComeFromBusinessSystem
     * @return bool|string
     */
    public static function saveContract(ContractManageModel $contractModel, $isComeFromBusinessSystem = false, $addition = [])
    {
        $contractManageEntity = new ContractManageEntity();

        $data = $contractModel->attributes;
        $contractManageEntity->load($data, '');
        if ($isComeFromBusinessSystem === false) {//如果不是来自商机系统，则合同ID由我们生成，如果是来自商机系统，合同ID由对方提供
            $contractManageEntity->contract_id = StringHelper::uuid();
        }

        if (!empty($addition)) {
            foreach ($addition as $key => $adData) {
                $contractManageEntity->$key = $adData;
            }
        }

        //合同编号规则  组合规则：区域（SH）-客户简称（客户拼音字母简称，从商机系统获取）- FW20150102流水（0001）
        if ($contractManageEntity->save()) {
            return $contractManageEntity->contract_id;
        } else {
            return false;
        }
    }

    /**
     * 更新合同
     * @param ContractManageModel $contractModel
     * @return string|boolean
     */
    public static function updateContract(ContractManageModel $contractModel, $addition = [])
    {

        $contractManageEntity = new ContractManageEntity();

        $contractModelInfo = $contractManageEntity::find()->where(['contract_id' => $contractModel->contract_id])->one();
        $data = $contractModel->attributes;

        $contractModelInfo->load($data, '');
        $contractModelInfo->is_violated = intval($contractModelInfo->is_violated);
        $contractModelInfo->modified_on = ConfigEntity::getDbNowDate();

        if (!empty($addition)) {
            foreach ($addition as $key => $adData) {
                $contractModelInfo->$key = $adData;
            }
        }

        if ($contractModelInfo->save()) {
            return $contractModelInfo->contract_id;
        } else {
            return false;
        }
    }

    /**
     * 合同是否已审批
     * @param string $contractId 合同id
     * @return boolean
     */
    public static function isApproveStatus($contractId)
    {
        return ContractManageEntity::find()->where(['contract_status' => ContractManageEntity::APPROVE_STATUS_NO_HANDLE, 'contract_id' => $contractId])->exists();
    }

    /**
     * 获取单条合同model
     * @param string $contractId 合同id
     * @return ContractManageEntity|null
     */
    public static function getContractManageModelByContractId($contractId)
    {
        return ContractManageEntity::find()->where(['contract_id' => $contractId])->one();
    }

    /**
     * 更改合同状态
     * @param string $contractId 合同id
     * @param int $status 合同状态
     * @return boolean
     */
    public static function updateContractStatus($contractId, $status)
    {
        return ContractManageEntity::updateAll(['contract_status' => $status], ['contract_id' => $contractId]);
    }

    /**
     * 更新合同录入日期
     * @param type $contractId
     */
    public static function updateContractInputDate($contractId)
    {
        $entity = ContractManageEntity::find()->where(['contract_id' => $contractId])->one();
        $entity->effect_date = date('Y-m-d');
        $entity->save();
    }

    /**
     * 合同作废
     * @param string $contractId 合同id
     * @return boolean
     */
    public static function updateContractCancelledStatusByContractId($contractId)
    {
        return ContractManageEntity::updateAll(['is_cancelled' => ContractManageEntity::CONTRACT_IS_CANCELLED,'modified_on'=>ConfigEntity::getDbNowDate()], ['contract_id' => $contractId]);
    }

    /**
     * 获取存在合同的城市区域id列表
     * @param $area_id 公司ID
     * @return array
     */
    public static function getContractAreaIdList($area_id)
    {
        $query = ContractManageEntity::find()->select('area_id');
        if (!empty($area_id)) {
            $query->andWhere(['area_id' => $area_id]);
        }
        return $query->groupBy('area_id')->all();
    }

    /**
     * 获取合同列表
     * @param $pageParam
     * @param int $status 合同状态  合同状态:0：草稿,1：提交复核,2：已审核  -1作废
     * @param string $property 合同性质
     * @param string $keyword 关键字
     * @param integer $areaId 区域id
     * @param string $signPeopleId 签约人ID
     * @param $departmentId 部门id
     * @param string $invoiceType 0 发票 1收据 2发票和收据
     * @return array
     */
    public static function getContractList($pageParam, $BusinessType, $status, $property, $keyword, $areaId, $invoiceType, $signPeopleId, $departmentId, $beginInputDate, $endInputDate,$isOver)
    {
        $query = ContractManageEntity::find();

        $query->where("business_type_id != 'f851bc86-54ca-11e7-9f96-000c29310f8b'");//釆宝单独模块展示

        if ($status !== '') {//合同状态
            if ($status == -1) {
                $query->andWhere(['is_cancelled' => ContractManageEntity::CONTRACT_IS_CANCELLED]);
            } else {
                $query->andWhere(['is_cancelled' => ContractManageEntity::CONTRACT_NO_CANCELLED]);
                $query->andWhere(['contract_status' => $status]);
            }
        } else {//合同状态为 全部 时，不包含已作废
            $query->andWhere(['is_cancelled' => ContractManageEntity::CONTRACT_NO_CANCELLED]);
        }

        if(!empty($BusinessType)){
            $query->andWhere(['business_type_id' => $BusinessType]);
        }

        if (!empty($property)) {//合同性质
            $query->andWhere(['contract_property_id' => $property]);
        }

        if (!empty($signPeopleId)) { //签约人
            $query->andWhere(['sign_people_id' => $signPeopleId]);
        }

        if (!empty($departmentId)) { //签约人所属部门
            $query->andWhere(['sign_people_department_id' => $departmentId]);
        }

        if (!empty($keyword)) {//搜索关键字  公司/公司简称/合同编号/客户全称
            $query->leftJoin('myscrm_contract_company', 'myscrm_contract_manage.company_id = myscrm_contract_company.company_id');
            self::setKeywordQuery($query, $keyword);
        }

        if (!empty($areaId)) {//区域id
            $query->andWhere(['area_id' => $areaId]);
        }

        if(!empty($isOver) || $isOver === "0"){
            $query->andWhere(['isover' => $isOver]);
        }

        //录入时间过滤
        if (!empty($beginInputDate)) {
            $query->andWhere(['>=', 'input_date', $beginInputDate]);
        }

        if (!empty($endInputDate)) {
            $query->andWhere(['<=', 'input_date', $endInputDate]);
        }

        self::setInvoiceTypeQuery($query, $invoiceType);

        $query->orderBy(['input_date'=>SORT_DESC]);   //录入金额降序排列

        $count = $query->count(); //条数
        $offset = Helper::getSkipByPageParam($pageParam);
        $query->offset($offset)->limit($pageParam->pageSize); //分页
        $items = $query->asArray()->all();
        return Helper::setPageResult($count, $items);
    }

    /**
     *取云链直销合同的签约业绩合同明细数据
     */
    public static function getDirectContract($beginTime = '',$endTime = ''){
        $sql = "SELECT
                m.contract_id,
                t.business_type_name,
                p.property_name,
                '云链' bu_name,
                m.input_date,
                t.business_type_id,
                case t.business_type_id
                when 'f85729eb-54ca-11e7-9f96-000c29310f8b' then m.supplier_name
                when 'f851bc86-54ca-11e7-9f96-000c29310f8b' then m.supplier_name
                when 'f85342fe-54ca-11e7-9f96-000c29310f8b' then m.supplier_name
                when 'f854fa59-54ca-11e7-9f96-000c29310f8b' then m.activity_name
                else m.customer_full_name end customer_full_name,
                m.customer_name,
                m.top_customer_name,
                IF(IFNULL(m.sign_people_id,'')='',m.sign_people_name,us.UserName) sign_people_name,
                m.sign_date,
                u.BUName area_name,
                m.contract_code,
                m.sign_amount,
                t.finance_type_name,
                t.type_name,
                m.link_contract_id,
				u.BUGUID as department_guid
                FROM myscrm_contract_manage m
                inner join myscrm_contract_business_type t on m.business_type_id=t.business_type_id
                inner join myscrm_contract_property p on m.contract_property_id=p.property_id
                left join myscrm_erp_business_unit u on m.area_id=u.BUGUID
                left join myscrm_erp_user us on m.sign_people_id=us.UserGUID
                left join (
                    SELECT r.contract_id,t.finance_type_name,t.type_name FROM myscrm_contract_revenue r
                    inner join myscrm_buss_account_types t on r.revenue_type=t.type_id
                    group by r.contract_id
                ) t on m.contract_id=t.contract_id
                WHERE m.contract_status=2 AND m.is_cancelled=0 ";
        if(!empty($beginTime) && !empty($endTime)){
            $sql .= " AND m.input_date BETWEEN :bgtime AND :endtime";
        }
        $data = ContractManageEntity::getDb()->createCommand($sql,[':bgtime'=>$beginTime,':endtime'=>$endTime])->queryAll();
        return $data;
    }

    /**
     *取云链直销合同的回款业绩明细数据
     */
    public static function getDirectContractPayment($beginTime = '',$endTime = ''){
        $sql = "SELECT
                p.payment_id,
                t.business_type_name sign_product,
                m.contract_id,
                t.business_type_name,
                '云链' bu_name,
                pro.property_name,
                t.business_type_id,
                case t.business_type_id
                when 'f85729eb-54ca-11e7-9f96-000c29310f8b' then m.supplier_name
                when 'f851bc86-54ca-11e7-9f96-000c29310f8b' then m.supplier_name
                when 'f85342fe-54ca-11e7-9f96-000c29310f8b' then m.supplier_name
                when 'f854fa59-54ca-11e7-9f96-000c29310f8b' then m.activity_name
                else m.customer_full_name end customer_full_name,
                m.customer_name,
                m.top_customer_name,
                IF(IFNULL(m.sign_people_id,'')='',m.sign_people_name,us.UserName) sign_people_name,
                u.BUName area_name,
                m.sign_date,
                m.sign_amount,
                m.input_date,
                m.contract_code,
                t.busi_type_name,
                t.finance_type_name,
                IF(IFNULL(cr.contract_property,'0')=1,'续签','新增') receiva_type,
                p.payment_date,
                p.payment_amount,
                p.input_date as payment_input_date,
                '云链' organize,
                m.link_contract_id
                FROM myscrm_contract_manage m
                inner join myscrm_contract_business_type t on m.business_type_id=t.business_type_id
                inner join myscrm_contract_payment p on m.contract_id=p.contract_id
                left join myscrm_contract_property pro on m.contract_property_id=pro.property_id
                left join (
                    select DISTINCT recr.contract_property,ip.payment_id from  myscrm_invoice_payment ip 
                    left join myscrm_invoice_receivable ir on ip.invoice_receivable_id=ir.invoice_receivable_id
                    left join myscrm_contract_receivable recr on ir.receivable_id=recr.receivable_id
				) cr
				on p.payment_id=cr.payment_id 
                left join myscrm_erp_business_unit u on m.area_id=u.BUGUID
                left join myscrm_erp_user us on m.sign_people_id=us.UserGUID
                left join (
                        SELECT r.contract_id,t.type_name busi_type_name,t.finance_type_name FROM myscrm_contract_revenue r
                        inner join myscrm_buss_account_types t on r.revenue_type=t.type_id
                        group by r.contract_id
                ) t on m.contract_id=t.contract_id
                WHERE m.contract_status=2 AND m.is_cancelled=0 ";

        if(!empty($beginTime) && !empty($endTime)){
            $sql .= " AND p.input_date BETWEEN :bgtime AND :endtime";
        }
        $data = ContractManageEntity::getDb()->createCommand($sql,[':bgtime'=>$beginTime,':endtime'=>$endTime])->queryAll();
        return $data;
    }

    /**
     * 查询发票类型：0 发票 1收据 2发票和收据
     * @param \Yii\db\Query $query
     * @param string $invoiceType
     */
    public static function setInvoiceTypeQuery(&$query, $invoiceType)
    {
        //查询条件is_receipt：0发票 1收据
        switch ($invoiceType) {
            case '0'://只有发票
                $query->andWhere('myscrm_contract_manage.contract_id in
                        (
                            SELECT DISTINCT contract_id from myscrm_invoice where is_receipt=0 and IFNULL(contract_id,\'\')<>\'\'
                        )
                        and myscrm_contract_manage.contract_id not in
                        (
                            SELECT DISTINCT contract_id from myscrm_invoice where is_receipt=1 and IFNULL(contract_id,\'\')<>\'\'
                        )');
                break;
            case '1'://只有收据
                $query->andWhere('myscrm_contract_manage.contract_id not in
                        (
                            SELECT DISTINCT contract_id from myscrm_invoice where is_receipt=0 and IFNULL(contract_id,\'\')<>\'\'
                        )
                        and myscrm_contract_manage.contract_id in
                        (
                            SELECT DISTINCT contract_id from myscrm_invoice where is_receipt=1 and IFNULL(contract_id,\'\')<>\'\'
                        )');
                break;
            case '2'://发票和收据都有
                $query->andWhere('myscrm_contract_manage.contract_id in
                        (
                            SELECT DISTINCT contract_id from myscrm_invoice where is_receipt=0 and IFNULL(contract_id,\'\')<>\'\'
                        )
                        and myscrm_contract_manage.contract_id in
                        (
                            SELECT DISTINCT contract_id from myscrm_invoice where is_receipt=1 and IFNULL(contract_id,\'\')<>\'\'
                        )');
                break;
            default:
                break;
        }
    }

    /**
     * 获取釆宝类合同列表数据
     * @param $pageParam
     * @param $beginInputDate
     * @param $endInputDate
     */
    public static function getContractCaibaoList($pageParam, $beginInputDate, $endInputDate,$gettype = 'list'){
        $query = ContractManageEntity::find();

        $query->where(['business_type_id'=>'f851bc86-54ca-11e7-9f96-000c29310f8b']);
        //录入时间过滤
        if (!empty($beginInputDate)) {
            $query->andWhere(['>=', 'input_date', $beginInputDate]);
        }

        if (!empty($endInputDate)) {
            $query->andWhere(['<=', 'input_date', $endInputDate]);
        }
        $query->orderBy(['effect_date'=>SORT_DESC]);   //录入金额降序排列

        $count = $query->count(); //条数
        if($gettype != 'export') {
            $offset = Helper::getSkipByPageParam($pageParam);
            $query->offset($offset)->limit($pageParam->pageSize); //分页
        }
        $items = $query->asArray()->all();
        return Helper::setPageResult($count, $items);
    }

    /**
     * 获取釆宝类合同充值列表数据
     * @param $pageParam
     * @param $contract_id
     */
    public static function getContractCaibaoListMx($pageParam, $contract_id,$gettype = 'list'){
        $query = ContractManageEntity::find();

        $query->where(['contract_id'=> $contract_id]);

        $Manage = $query->asArray()->all();

        $query = ContractPayinfoEntity::find();
        $query->where(['contract_id'=> $contract_id]);

        //录入时间过滤
        if (!empty($beginInputDate)) {
            $query->andWhere(['>=', 'created_on', $Manage['input_date']]);
        }

        if (!empty($endInputDate)) {
            $query->andWhere(['<=', 'created_on', $a = date('Y-m-27 23:59:59', strtotime($Manage['input_date'].'-1 month'))]);
        }
        //$query->orderBy(['created_on'=>SORT_DESC]);   //录入金额降序排列

        $count = $query->count(); //条数
        if($gettype != 'export') {
            $offset = Helper::getSkipByPageParam($pageParam);
            $query->offset($offset)->limit($pageParam->pageSize); //分页
        }
        $items = $query->asArray()->all();
        return Helper::setPageResult($count, $items);
    }

    public static function getContractCaibaoDetailList($contractid){
        $query = ContractCaibaoDetailEntity::find()->where(['contract_id'=>$contractid])->all();
        return $query;
    }

    public static function getContractCaibaoInvoice($contractid){
        $sql = "SELECT
                myscrm_invoice.contract_id,
                myscrm_invoice.invoice_code,
                myscrm_invoice.invoice_no,
                myscrm_invoice.invoice_amount,
                myscrm_invoice.invoice_date,
                case when myscrm_invoice.invoice_category=1 then '服务票' else '销售票' end invoice_category,
                case when myscrm_invoice.invoice_type=1 then '增值税专用发票' else '增值税普通发票' end invoice_type,
                invoiceCompany.BUName,
                invoiceUser.UserName,
                invoiceUser.UserCode
                FROM myscrm_invoice
                left join  myscrm_erp_user invoiceUser on myscrm_invoice.proposer_id = invoiceUser.UserGUID
                left join myscrm_invoice_business_unit invoiceCompany on myscrm_invoice.invoice_company_id = invoiceCompany.BUGUID
                where myscrm_invoice.contract_id='".$contractid."'";
        $query = InvoiceEntity::getDb()->createCommand($sql)->queryAll();
        return $query;
    }

    public static function getContractListByCode($code_list)
    {
        return ContractManageEntity::find()->select("contract_id,contract_code")->where(['is_cancelled' => 0])->andWhere(['in', 'contract_code', $code_list])->asArray()->all();
    }

    /**
     * 获取结算合同列表
     * @param $pageParam 分页参数对象
     * @param int $status 结算状态 0：未结算 1：已结算
     * @param string $property 合同性质
     * @param integer $areaId 区域id
     * @param string $keyword 关键字
     * @param string $beginDate 结算开始时间
     * @param string $endDate 结算结束时间
     */
    public static function getBalanceContractList($pageParam,$BusinessType,$backstatus, $status, $property, $keyword, $areaId, $beginDate, $endDate)
    {
        $query = ContractManageEntity::find();
        $query->where(['business_type_id'=>['f85b3855-54ca-11e7-9f96-000c29310f8b','f8592797-54ca-11e7-9f96-000c29310f8b','9a96f3dc-54ca-11e7-9f96-000c29310f8b','f854fa59-54ca-11e7-9f96-000c29310f8b']]);

        if ($BusinessType !== '') {//合同业务类型
            $query->andWhere(['business_type_id' => $BusinessType]);
        }

        if ($backstatus !== '') {//合同返款状态
            $query->andWhere(['back_status' => $backstatus]);
        }

        if ($status !== '') {//合同结算状态
            $query->andWhere(['balance_status' => $status]);
        }

        if (!empty($property)) {//合同性质
            $query->andWhere(['contract_property_id' => $property]);
        }

        if (!empty($keyword)) {//搜索关键字  公司/公司简称/合同编号/客户全称
            $query->leftJoin('myscrm_contract_company', 'myscrm_contract_manage.company_id= myscrm_contract_company.company_id');
            self::setKeywordQuery($query, $keyword);
        }

        if (!empty($areaId)) {//区域id
            $query->andWhere(['area_id' => $areaId]);
        }

        if (!empty($beginDate)) {//结算时间-开始时间
            $query->andWhere('balance_date >=' . "'" . $beginDate . "'");
        }

        if (!empty($endDate)) {//结算时间-结束时间
            $query->andWhere('balance_date <=' . "'" . $endDate . "'");
        }
        $query->andWhere(['is_cancelled' => ContractManageEntity::CONTRACT_NO_CANCELLED]);

        $count = $query->count(); //条数
        $offset = Helper::getSkipByPageParam($pageParam);
        $query->offset($offset)->limit($pageParam->pageSize); //分页
        $items = $query->asArray()->all();
        return Helper::setPageResult($count, $items);
    }

    /**
     * 获取导出未结算合同列表
     * @param boolean $property 合同性质
     * @param integer $areaId 区域id
     * @param string $keyword 关键字
     * @param string $beginDate 结算开始时间
     * @param string $endDate 结算结束时间
     */
    public static function getExportBalanceContractListExecl($property, $keyword, $areaId, $beginDate, $endDate, $status,$backstatus)
    {
        $query = ContractManageEntity::find();
        $query->where(['business_type_id'=>['f85b3855-54ca-11e7-9f96-000c29310f8b','f8592797-54ca-11e7-9f96-000c29310f8b','9a96f3dc-54ca-11e7-9f96-000c29310f8b','f854fa59-54ca-11e7-9f96-000c29310f8b']]);

        if ($backstatus !== '') {//合同返款状态
            $query->andWhere(['back_status' => $backstatus]);
        }

        if ($status !== '') {//合同结算状态
            $query->andWhere(['balance_status' => $status]);
        }

        if (!empty($property)) {//合同性质
            $query->andWhere(['contract_property_id' => $property]);
        }
        $query->leftJoin('myscrm_erp_business_unit', 'myscrm_contract_manage.company_id = myscrm_erp_business_unit.BUGUID');
        if (!empty($keyword)) {//搜索关键字  公司/公司简称/合同编号
            $query->andWhere(['or',
                ['like', 'myscrm_contract_manage.customer_full_name', $keyword],
                ['like', 'myscrm_erp_business_unit.BUName', $keyword],
                ['like', 'myscrm_contract_manage.contract_code', $keyword]]);
        }

        if (!empty($areaId)) {//区域id
            $query->andWhere(['area_id' => $areaId]);
        }

        if (!empty($beginDate)) {//结算时间-开始时间
            $query->andWhere('balance_date >=' . "'" . $beginDate . "'");
        }

        if (!empty($endDate)) {//结算时间-结束时间
            $query->andWhere('balance_date <=' . "'" . $endDate . "'");
        }
        $query->andWhere(['is_cancelled' => ContractManageEntity::CONTRACT_NO_CANCELLED]);

        return $query->asArray()->all();
    }

    /**
     * 特批授权界面获取合同select2控件下拉选项
     * @param string $keyword 搜索关键字
     * @param string $orgId 租户Id
     * @param string $page 分页
     */
    public static function getContractListForSepcialAuthorizeSelect2($keyword, $orgId, $limit = 10)
    {
        if (is_null($keyword) || empty($keyword)) {
            return [];
        }

        return ContractManageEntity::find()
            ->select('contract_id,contract_code as id,contract_code as text')
            ->where(['like', 'contract_code', $keyword])
            //->andWhere(['myscrm_contract.OrganizationId' => $orgId])
            //813付小玲需求，已绑定的合同还可以选
            //->andWhere(['not exists', $query->select('contract_code')->from('myscrm_special_approval_authorize')->where('myscrm_special_approval_authorize.contract_code=myscrm_contract_manage.contract_code')])
            ->orderBy(['contract_code' => SORT_ASC])
            ->limit($limit)
            ->asArray()
            ->all();
    }

    /**
     * 违规合同界面获取合同select2控件下拉选项
     * @param string $keyword 搜索关键字
     * @param string $orgId 租户Id
     * @param string $page 分页
     */
    public static function getContractListForViolatedContractSelect2($keyword, $limit = 10)
    {
        if (is_null($keyword) || empty($keyword)) {
            return [];
        }

        $query = new Query();
        return ContractManageEntity::find()
            ->select('contract_id,contract_code as id,contract_code as text')
            ->where(['like', 'contract_code', $keyword])
            ->orderBy(['contract_code' => SORT_ASC])
            ->limit($limit)
            ->asArray()
            ->all();
    }

    /**
     * 检查是否已存在合同编号
     * @param string $contractCode 合同编号
     * @return boolean
     */
    public static function isExistContractCodeByContractCode($contractCode)
    {
        return ContractManageEntity::find()->where(['contract_code' => $contractCode])->exists();
    }

    /**
     * 处理合同结算状态
     * @param string $contractId 合同id
     * @param int $status 合同结算状态
     * @return boolean
     */
    public static function handleContractBalanceStatus($contractId, $status)
    {
        $status = intval($status);
        $contractManageEntity = self::getContractManageModelByContractId($contractId);
        if ($contractManageEntity->balance_status == $status) {
            return true;
        } else {
            $contractManageEntity->balance_status = $status; //结算状态
            if ($status == ContractManageEntity::CONTRACT_BALANCE_STATUS) {
                $contractManageEntity->balance_date = ConfigEntity::getDbNowDate(); //结算时间
            }
            return $contractManageEntity->save();
        }
    }

    /**
     * 设置关键字查询
     * @param type $query
     * @param type $keyword
     */
    public static function setKeywordQuery(&$query, $keyword)
    {
        if (!empty($keyword)) {//搜索关键字  客户全称/公司/合同编号
            $query->andWhere(['or',
                ['like', 'myscrm_contract_manage.customer_full_name', $keyword],
                ['like', 'myscrm_contract_manage.customer_name', $keyword],
                ['like', 'myscrm_contract_manage.supplier_name', $keyword],
                ['like', 'myscrm_contract_manage.activity_name', $keyword],
                ['like', 'myscrm_contract_manage.erp_contract_code', $keyword],
                ['like', 'myscrm_contract_company.company_name', $keyword],
                ['like', 'myscrm_contract_manage.contract_code', $keyword]]);
        }
    }

    /**
     * 导出合同
     */
    public static function getContractListForExport($status, $property, $keyword, $areaId, $signPeopleId,$businesstype)
    {
        $query = ContractManageEntity::find()
            ->select('myscrm_contract_manage.*,myscrm_contract_business_type.business_type_name,sumpayment.payment_amount,myscrm_contract_property.property_name');
        $query = self::setStatusCondition($status, $query);

        if (!empty($property)) {//合同性质
            $query->andWhere(['contract_property_id' => $property]);
        }

        $query->leftJoin('myscrm_contract_company', 'myscrm_contract_manage.company_id = myscrm_contract_company.company_id')
            ->leftJoin('myscrm_contract', 'myscrm_contract.ContractNum=myscrm_contract_manage.contract_code')
            ->leftJoin('myscrm_contract_business_type','myscrm_contract_business_type.business_type_id=myscrm_contract_manage.business_type_id')
            ->leftJoin('(SELECT contract_id,SUM(payment_amount) payment_amount FROM myscrm_contract_payment group by contract_id) sumpayment','sumpayment.contract_id=myscrm_contract_manage.contract_id')->leftJoin('myscrm_contract_property', 'myscrm_contract_property.property_id=myscrm_contract_manage.contract_property_id');

        // 搜索关键字
        self::setKeywordQuery($query, $keyword);

        if (!empty($areaId)) {//区域id
            $query->andWhere(['area_id' => $areaId]);
        }

        if (!empty($signPeopleId)) {//签约人
            $query->andWhere(['sign_people_id' => $signPeopleId]);
        }

        if (!empty($businesstype)) {//合同类型
            $query->andWhere(['myscrm_contract_manage.business_type_id' => $businesstype]);
        }

        $query->orderBy(['input_date'=>SORT_DESC]);

        return $query->asArray()->all();
    }

    private static function setStatusCondition($status, ActiveQuery $query)
    {
        if ($status !== '') {//合同状态
            $query->andWhere(['is_cancelled' => ContractManageEntity::CONTRACT_NO_CANCELLED]);
            if ($status == -1) {
                $query->andWhere(['is_cancelled' => ContractManageEntity::CONTRACT_IS_CANCELLED]);
            } else {
                $query->andWhere(['contract_status' => $status]);
            }
        } else {//合同状态为 全部 时，不包含已作废
            $query->andWhere(['is_cancelled' => ContractManageEntity::CONTRACT_NO_CANCELLED]);
            $query->andWhere(['<>', 'contract_status', 0]);
        }

        return $query;
    }

    /**
     * 导出合同产品
     */
    public static function getContractProductListForExport($status, $property, $keyword, $areaId, $signPeopleId,$businesstype)
    {
        $query = ContractManageEntity::find()
            ->select("myscrm_contract_manage.contract_id,myscrm_contract_manage.customer_name,myscrm_contract_manage.customer_full_name,
            myscrm_contract_manage.top_customer_name,myscrm_contract_manage.erp_contract_code,myscrm_contract_manage.contract_code,
            myscrm_contract_manage.soure_financial,myscrm_contract_manage.soure_business,myscrm_contract_manage.sign_date,
            myscrm_contract_manage.input_date,myscrm_contract_manage.sign_amount,myscrm_contract_manage.rent_amount,myscrm_contract_manage.business_type_id,
            myscrm_contract_manage.sign_people_id,myscrm_contract_manage.area_id,myscrm_contract_manage.link_contract_id,
            payover.authnums,payover.authtime,payover.balanamount,
            myscrm_contract_property.property_name,
            application.*,
            myscrm_contract_company.company_name,
            myscrm_contract_account_company.account_company_name,
            bt.business_type_name");
        $query = self::setStatusCondition($status, $query);

        if (!empty($property)) {
            //合同性质
            $query->andWhere(['contract_property_id' => $property]);
        }

        $query->innerJoin('(
                                SELECT a.unit,a.contract_application_id,i.application_name,i.application_id,contract_id,authorization_numeric,amount,rent_share_amount,balance_share_amount,balance_should_amount
                                FROM myscrm_contract_application a
                                inner join myscrm_application_info i on a.application_id=i.application_id
                                UNION ALL
                                SELECT \'\' unit,product_id contract_application_id,pname application_name,
                                case p.pname when \'移动工程协同充值卡\' then \'0df6297e-e421-4a35-aa34-497c59a529ba\' else \'\' end
                                 application_id,contract_id,0 authorization_numeric,totalamount amount,0 rent_share_amount,0 balance_share_amount,0 balance_should_amount FROM myscrm_contract_product p
                            ) application', 'application.contract_id= myscrm_contract_manage.contract_id')
            ->leftJoin('myscrm_contract_property','myscrm_contract_property.property_id=myscrm_contract_manage.contract_property_id')
            ->leftJoin('myscrm_contract_company', 'myscrm_contract_manage.company_id = myscrm_contract_company.company_id')
            ->leftJoin('myscrm_contract_business_type bt', 'myscrm_contract_manage.business_type_id= bt.business_type_id')
            ->leftJoin('myscrm_contract_account_company','myscrm_contract_account_company.account_company_id=myscrm_contract_manage.account_company_id')
            ->leftJoin('(SELECT contract_id,application_id,SUM(authorization_numeric) authnums,MIN(authorize_time) authtime,SUM(balance_amount) balanamount FROM myscrm_contract_payover GROUP BY contract_id,application_id) payover','payover.application_id=application.application_id and payover.contract_id=myscrm_contract_manage.contract_id');

        // 搜索关键字
        self::setKeywordQuery($query, $keyword);

        if (!empty($areaId)) {//区域id
            $query->andWhere(['area_id' => $areaId]);
        }

        if (!empty($signPeopleId)) {//签约人
            $query->andWhere(['sign_people_id' => $signPeopleId]);
        }

        if (!empty($businesstype)) {//合同类型
            $query->andWhere(['myscrm_contract_manage.business_type_id' => $businesstype]);
        }

        $query->orderBy(['contract_code' => SORT_ASC, 'application_name' => SORT_ASC]);

        $query->indexBy('contract_application_id');
        return $query->asArray()->all();
    }

    /**
     * 导出合同产品开通信息
     */
    public static function getContractProductKtListForExport($status, $property, $keyword, $areaId, $signPeopleId,$businesstype)
    {
        $query = ContractManageEntity::find()
            ->select("myscrm_contract_manage.contract_id,myscrm_contract_manage.input_date,myscrm_contract_tenant_relation.kt_date,myscrm_contract_manage.customer_name,myscrm_contract_manage.customer_full_name,
            myscrm_contract_manage.erp_contract_code,myscrm_contract_manage.effect_date,myscrm_contract_manage.sign_date,myscrm_contract_manage.sign_amount,myscrm_contract_manage.business_type_id,
            myscrm_contract_manage.sign_people_id,myscrm_erp_user.UserName,bt.business_type_name,myscrm_contract_property.property_name,
            po.*,myscrm_erp_business_unit.BUName area_name,myscrm_application_info.LicensingStandards");

        $query->innerJoin('myscrm_contract_payover po', 'po.contract_id= myscrm_contract_manage.contract_id')
            ->leftJoin('myscrm_application_info','myscrm_application_info.application_id=po.application_id')
            ->leftJoin('myscrm_contract_property','myscrm_contract_property.property_id=myscrm_contract_manage.contract_property_id')
            ->leftJoin('myscrm_contract_business_type bt', 'myscrm_contract_manage.business_type_id= bt.business_type_id')
            ->leftJoin('myscrm_contract_company','myscrm_contract_company.company_id=myscrm_contract_manage.company_id')
            ->leftJoin('myscrm_erp_business_unit','myscrm_contract_manage.area_id=myscrm_erp_business_unit.BUGUID')
            ->leftJoin('myscrm_contract_tenant_relation','po.contract_id=myscrm_contract_tenant_relation.contract_id and po.tenant_id=myscrm_contract_tenant_relation.tenant_id')
            ->leftJoin('myscrm_erp_user','myscrm_erp_user.UserGUID=myscrm_contract_manage.sign_people_id');

        $query = self::setStatusCondition($status, $query);

        $query->andWhere("myscrm_contract_manage.business_type_id!='f85342fe-54ca-11e7-9f96-000c29310f8b'");

        $query->andWhere("myscrm_contract_manage.business_type_id!='9a96f3dc-54ca-11e7-9f96-000c29310f8b'");

        if (!empty($property)) {
            //合同性质
            $query->andWhere(['contract_property_id' => $property]);
        }
        // 搜索关键字
        self::setKeywordQuery($query, $keyword);

        if (!empty($areaId)) {//区域id
            $query->andWhere(['area_id' => $areaId]);
        }

        if (!empty($signPeopleId)) {//签约人
            $query->andWhere(['sign_people_id' => $signPeopleId]);
        }

        if (!empty($businesstype)) {//合同类型
            $query->andWhere(['myscrm_contract_manage.business_type_id' => $businesstype]);
        }

        $query->orderBy(['contract_code' => SORT_ASC]);

        $query->indexBy('contract_payover_id');
        return $query->asArray()->all();
    }

    /**
     * 获取合同结算信息
     * @param $status
     * @param $property
     * @param $keyword
     * @param $areaId
     * @param $signPeopleId
     * @return array|\yii\db\ActiveRecord[]
     */
    public static function getContractBalanceListForExport($status, $property, $keyword, $areaId, $signPeopleId,$businesstype){
        $query = ContractManageEntity::find()
            ->select("myscrm_contract_manage.contract_id,myscrm_contract_manage.customer_name,myscrm_contract_manage.customer_full_name,myscrm_contract_manage.contract_code"
                . ",myscrm_contract_manage.business_type_id,payover.*,bt.business_type_name");
        $query = self::setStatusCondition($status, $query);

        if (!empty($property)) {
            //合同性质
            $query->andWhere(['contract_property_id' => $property]);
        }

        $query->innerJoin('myscrm_contract_payover payover', 'payover.contract_id= myscrm_contract_manage.contract_id')
            ->leftJoin('myscrm_contract_company', 'myscrm_contract_manage.company_id = myscrm_contract_company.company_id')
            ->leftJoin('myscrm_erp_business_unit contractArea', 'myscrm_contract_manage.area_id= contractArea.BUGUID')
            ->leftJoin('myscrm_contract_business_type bt', 'myscrm_contract_manage.business_type_id= bt.business_type_id');

        // 搜索关键字
        self::setKeywordQuery($query, $keyword);

        if (!empty($areaId)) {//区域id
            $query->andWhere(['area_id' => $areaId]);
        }

        if (!empty($signPeopleId)) {//签约人
            $query->andWhere(['sign_people_id' => $signPeopleId]);
        }

        if (!empty($businesstype)) {//合同类型
            $query->andWhere(['myscrm_contract_manage.business_type_id' => $businesstype]);
        }

        $query->orderBy(['myscrm_contract_manage.contract_code' => SORT_ASC, 'payover.application_name' => SORT_ASC]);
        $query->indexBy('contract_payover_id');
        return $query->asArray()->all();
    }

    /**
     * 导出合同收入
     */
    public static function getContractRevenueListForExport($status, $property, $keyword, $areaId, $signPeopleId,$businesstype)
    {
        $query = ContractManageEntity::find()
            ->select("myscrm_contract_manage.contract_id,myscrm_contract_manage.customer_name,myscrm_contract_manage.customer_full_name,myscrm_contract_manage.contract_code"
                . ",myscrm_contract.BeginOn,myscrm_contract.EndOn, myscrm_contract_manage.contract_property_id, myscrm_contract_manage.category_id"
                . ",myscrm_contract_manage.littler_category_id,myscrm_contract_manage.business_type_id"
                . ",contractArea.BUName as area_name,myscrm_buss_account_types.type_name,revenue.*,bt.business_type_name,myscrm_contract_manage.business_type_id,myscrm_contract_manage.orderid");
        $query = self::setStatusCondition($status, $query);

        if (!empty($property)) {
            //合同性质
            $query->andWhere(['contract_property_id' => $property]);
        }

        $query->innerJoin('myscrm_contract_revenue revenue', 'revenue.contract_id= myscrm_contract_manage.contract_id')
            ->leftJoin('myscrm_buss_account_types', 'myscrm_buss_account_types.type_id=revenue.revenue_type')
            ->leftJoin('myscrm_contract', 'myscrm_contract.ContractNum=myscrm_contract_manage.contract_code')
            ->leftJoin('myscrm_contract_company', 'myscrm_contract_manage.company_id = myscrm_contract_company.company_id')
            ->leftJoin('myscrm_erp_business_unit contractArea', 'myscrm_contract_manage.area_id= contractArea.BUGUID')
            ->leftJoin('myscrm_contract_business_type bt', 'myscrm_contract_manage.business_type_id= bt.business_type_id');

        // 搜索关键字
        self::setKeywordQuery($query, $keyword);

        if (!empty($areaId)) {//区域id
            $query->andWhere(['area_id' => $areaId]);
        }

        if (!empty($signPeopleId)) {//签约人
            $query->andWhere(['sign_people_id' => $signPeopleId]);
        }

        if (!empty($businesstype)) {//合同类型
            $query->andWhere(['myscrm_contract_manage.business_type_id' => $businesstype]);
        }

        $query->orderBy(['contract_code' => SORT_ASC, 'revenue_type' => SORT_ASC]);
        $query->indexBy('revenue_id');
        return $query->asArray()->all();
    }

    /**
     * 导出合同应收款项
     */
    public static function getContractReceivableListForExport($status, $property, $keyword, $areaId, $signPeopleId,$businesstype)
    {
        $query = ContractManageEntity::find()
            ->select("myscrm_contract_manage.contract_id,myscrm_contract_manage.customer_name,myscrm_contract_manage.customer_full_name,myscrm_contract_manage.contract_code"
                . ",receivablePayee.UserName as payee_name"
                . ",receivableItem.receivable_item_name,categoryItem.money_category_item_name"
                . ",myscrm_contract_manage.area_id,myscrm_buss_account_types.type_name as revenue_type_name,receivable.*,bt.business_type_name");
        $query = self::setStatusCondition($status, $query);

        if (!empty($property)) {
            //合同性质
            $query->andWhere(['contract_property_id' => $property]);
        }

        $query->innerJoin('myscrm_contract_receivable receivable', 'receivable.contract_id= myscrm_contract_manage.contract_id')
            ->leftJoin('myscrm_receivable_item receivableItem', 'receivable.receivable_item_id = receivableItem.receivable_item_id')
            ->leftJoin('myscrm_money_category_item categoryItem', 'receivable.money_category_item_id = categoryItem.money_category_item_id')
            ->leftJoin('myscrm_buss_account_types', 'receivable.revenue_type = myscrm_buss_account_types.type_id')
            ->leftJoin('myscrm_erp_user receivablePayee', 'receivable.payee_id = receivablePayee.UserGUID')
            ->leftJoin('myscrm_contract_company', 'myscrm_contract_manage.company_id = myscrm_contract_company.company_id')
            ->leftJoin('myscrm_contract_business_type bt', 'myscrm_contract_manage.business_type_id= bt.business_type_id');

        // 搜索关键字
        self::setKeywordQuery($query, $keyword);

        if (!empty($areaId)) {//区域id
            $query->andWhere(['area_id' => $areaId]);
        }

        if (!empty($signPeopleId)) {//签约人
            $query->andWhere(['sign_people_id' => $signPeopleId]);
        }

        if (!empty($businesstype)) {//合同类型
            $query->andWhere(['myscrm_contract_manage.business_type_id' => $businesstype]);
        }

        $query->orderBy(['contract_code' => SORT_ASC, 'revenue_type' => SORT_ASC]);
        $query->indexBy('receivable_id');
        return $query->asArray()->all();
    }

    /**
     * 导出合同发票
     */
    public static function getContractInvoiceListForExport($status, $property, $keyword, $areaId, $signPeopleId,$businesstype)
    {
        $query = ContractManageEntity::find()
            ->select("myscrm_contract_manage.contract_id,myscrm_contract_manage.customer_name,myscrm_contract_manage.customer_full_name,myscrm_contract_manage.contract_code"
                . ",myscrm_invoice.*,invoiceCompany.BUName,invoiceUser.UserName,invoiceUser.UserCode"
                . ",contractArea.BUName as area_name,bt.business_type_name");
        $query = self::setStatusCondition($status, $query);

        if (!empty($property)) {
            //合同性质
            $query->andWhere(['contract_property_id' => $property]);
        }

        $query->innerJoin('myscrm_invoice', 'myscrm_invoice.contract_id= myscrm_contract_manage.contract_id')
            ->leftJoin('myscrm_erp_user invoiceUser', 'myscrm_invoice.proposer_id = invoiceUser.UserGUID')
            ->leftJoin('myscrm_invoice_business_unit invoiceCompany', 'myscrm_invoice.invoice_company_id = invoiceCompany.BUGUID')
            ->leftJoin('myscrm_contract', 'myscrm_contract.ContractNum=myscrm_contract_manage.contract_code')
            ->leftJoin('myscrm_contract_company', 'myscrm_contract_manage.company_id = myscrm_contract_company.company_id')
            ->leftJoin('myscrm_erp_business_unit contractArea', 'myscrm_contract_manage.area_id= contractArea.BUGUID')
            ->leftJoin('myscrm_contract_business_type bt', 'myscrm_contract_manage.business_type_id= bt.business_type_id');

        // 搜索关键字
        self::setKeywordQuery($query, $keyword);

        if (!empty($areaId)) {//区域id
            $query->andWhere(['area_id' => $areaId]);
        }

        if (!empty($signPeopleId)) {//签约人
            $query->andWhere(['sign_people_id' => $signPeopleId]);
        }

        if (!empty($businesstype)) {//合同类型
            $query->andWhere(['myscrm_contract_manage.business_type_id' => $businesstype]);
        }

        $query->andWhere(['is_main_invoice' => 0]);
        $query->orderBy(['contract_code' => SORT_ASC, 'invoice_code' => SORT_ASC]);
        $query->indexBy('invoice_id');
        return $query->asArray()->all();
    }

    /**
     * 导出合同回款
     */
    public static function getContractPaymentListForExport($status, $property, $keyword, $areaId, $signPeopleId,$businesstype)
    {
        $query = ContractManageEntity::find()
            ->select("myscrm_contract_manage.*,myscrm_contract_company.company_name"
                . ",myscrm_contract_payment.payment_id,myscrm_contract_payment.finance_code,myscrm_contract_payment.payment_amount,myscrm_contract_payment.payment_date"
                . ",myscrm_contract_payment.input_date,myscrm_contract_payment.payment_code,myscrm_contract_payment.receivables_account,myscrm_contract_payment.receivables_account_name"
                . ",myscrm_contract_payment.payment_account_name,myscrm_contract_payment.withdraw_fee,(myscrm_contract_payment.payment_amount+myscrm_contract_payment.withdraw_fee) allamount"
                . ",contractArea.BUName as area_name,bt.business_type_name");
        $query = self::setStatusCondition($status, $query);

        if (!empty($property)) {
            //合同性质
            $query->andWhere(['contract_property_id' => $property]);
        }

        $query->innerJoin('myscrm_contract_payment', 'myscrm_contract_payment.contract_id= myscrm_contract_manage.contract_id')
            ->leftJoin('myscrm_contract_company', 'myscrm_contract_manage.company_id = myscrm_contract_company.company_id')
            ->leftJoin('myscrm_erp_business_unit contractArea', 'myscrm_contract_manage.area_id= contractArea.BUGUID')
            ->leftJoin('myscrm_contract_business_type bt', 'myscrm_contract_manage.business_type_id= bt.business_type_id');

        // 搜索关键字
        self::setKeywordQuery($query, $keyword);

        if (!empty($areaId)) {//区域id
            $query->andWhere(['area_id' => $areaId]);
        }

        if (!empty($signPeopleId)) {//签约人
            $query->andWhere(['sign_people_id' => $signPeopleId]);
        }

        if (!empty($businesstype)) {//合同类型
            $query->andWhere(['myscrm_contract_manage.business_type_id' => $businesstype]);
        }

        $query->orderBy(['contract_code' => SORT_ASC, 'payment_code' => SORT_ASC]);
        $query->indexBy('payment_id');

        return $query->asArray()->all();
    }

    /**
     * 导出合同签约业绩
     */
    public static function getContractSignAchievementListForExport($status, $property, $keyword, $areaId, $signPeopleId)
    {
        $query = ContractManageEntity::find()
            ->select("contractProperty.property_name,myscrm_contract_manage.contract_id,myscrm_contract_manage.customer_name,myscrm_contract_manage.customer_full_name,myscrm_contract_manage.contract_code"
                . ",myscrm_sign_achievement.*,achievementBelong.UserName as belong_name,achievementManager.UserName as manager_name,myscrm_contract_manage.sign_date"
                . ",achievementArea.BUName as achievementarea_name,achievementDepartment.BUName as department_name"
                . ",erpAchievementBelong.UserName as erp_belong_name, erpAchievementBelongAbu.BUName as erp_belong_abu, erpAchievementBelongAbuOwner.UserName as erp_belong_abu_owner"
                . ",erpAchievementBelongBUManager.UserName as erp_belong_bu_manager,erpAchievementBelongBu.BUName as erp_belong_bu"
                . ",contractArea.BUName as area_name");
        $query = self::setStatusCondition($status, $query);

        if (!empty($property)) {
            //合同性质
            $query->andWhere(['contract_property_id' => $property]);
        }

        $query->innerJoin('myscrm_sign_achievement', 'myscrm_sign_achievement.contract_id= myscrm_contract_manage.contract_id')
            ->leftJoin('myscrm_erp_user achievementBelong', 'achievementBelong.UserGUID= myscrm_sign_achievement.belong_by')
            ->leftJoin('myscrm_erp_user achievementManager', 'achievementManager.UserGUID= myscrm_sign_achievement.manager_by')
            ->leftJoin('myscrm_erp_business_unit achievementArea', 'achievementArea.BUGUID= myscrm_sign_achievement.area_by')
            ->leftJoin('myscrm_erp_business_unit achievementDepartment', 'achievementDepartment.BUGUID= myscrm_sign_achievement.department_by')
            ->leftJoin('myscrm_erp_user erpAchievementBelong', 'erpAchievementBelong.UserGUID= myscrm_sign_achievement.erp_achievement_belong')
            ->leftJoin('myscrm_erp_business_unit erpAchievementBelongAbu', 'erpAchievementBelongAbu.BUGUID= myscrm_sign_achievement.erp_achievement_belong_abu')
            ->leftJoin('myscrm_erp_user erpAchievementBelongAbuOwner', 'erpAchievementBelongAbuOwner.UserGUID= myscrm_sign_achievement.erp_achievement_belong_abu_owner')
            ->leftJoin('myscrm_erp_user erpAchievementBelongBUManager', 'erpAchievementBelongBUManager.UserGUID= myscrm_sign_achievement.erp_achievement_belong_bu_manager')
            ->leftJoin('myscrm_erp_business_unit erpAchievementBelongBu', 'erpAchievementBelongBu.BUGUID= myscrm_sign_achievement.erp_achievement_belong_bu')
            ->leftJoin('myscrm_contract_company', 'myscrm_contract_manage.company_id = myscrm_contract_company.company_id')
            ->leftJoin('myscrm_erp_business_unit contractArea', 'myscrm_contract_manage.area_id= contractArea.BUGUID')
            ->leftJoin('myscrm_contract_property contractProperty', 'myscrm_contract_manage.contract_property_id= contractProperty.property_id');

        // 搜索关键字
        self::setKeywordQuery($query, $keyword);

        if (!empty($areaId)) {//区域id
            $query->andWhere(['area_id' => $areaId]);
        }

        if (!empty($signPeopleId)) {//签约人
            $query->andWhere(['sign_people_id' => $signPeopleId]);
        }

        $query->orderBy(['contract_code' => SORT_ASC, 'myscrm_sign_achievement.created_on' => SORT_ASC]);
        $query->indexBy('achievement_id');
        return $query->asArray()->all();
    }

    /**
     * 导出合同回款业绩
     */
    public static function getContractPaymentAchievementListForExport($status, $property, $keyword, $areaId, $signPeopleId)
    {
        $query = ContractManageEntity::find()
            ->select("contractProperty.property_name,myscrm_contract_manage.contract_id,myscrm_contract_manage.customer_name,myscrm_contract_manage.customer_full_name,myscrm_contract_manage.contract_code"
                . ",myscrm_payment_achievement.*,achievementBelong.UserName as belong_name,achievementManager.UserName as manager_name"
                . ",myscrm_receivable_item.receivable_item_name,myscrm_money_category_item.money_category_item_name"
                . ",achievementArea.BUName as achievementarea_name,achievementDepartment.BUName as department_name"
                . ",erpAchievementBelong.UserName as erp_belong_name, erpAchievementBelongAbu.BUName as erp_belong_abu, erpAchievementBelongAbuOwner.UserName as erp_belong_abu_owner"
                . ",erpAchievementBelongBUManager.UserName as erp_belong_bu_manager,erpAchievementBelongBu.BUName as erp_belong_bu"
                . ",contractArea.BUName as area_name");
        $query = self::setStatusCondition($status, $query);

        if (!empty($property)) {
            //合同性质
            $query->andWhere(['contract_property_id' => $property]);
        }

        $query->innerJoin('myscrm_payment_achievement', 'myscrm_payment_achievement.contract_id= myscrm_contract_manage.contract_id')
            ->leftJoin('myscrm_erp_user achievementBelong', 'achievementBelong.UserGUID= myscrm_payment_achievement.belong_by')
            ->leftJoin('myscrm_erp_user achievementManager', 'achievementManager.UserGUID= myscrm_payment_achievement.manager_by')
            ->leftJoin('myscrm_erp_business_unit achievementArea', 'achievementArea.BUGUID= myscrm_payment_achievement.area_by')
            ->leftJoin('myscrm_erp_business_unit achievementDepartment', 'achievementDepartment.BUGUID= myscrm_payment_achievement.department_by')
            ->leftJoin('myscrm_erp_user erpAchievementBelong', 'erpAchievementBelong.UserGUID= myscrm_payment_achievement.erp_achievement_belong')
            ->leftJoin('myscrm_erp_business_unit erpAchievementBelongAbu', 'erpAchievementBelongAbu.BUGUID= myscrm_payment_achievement.erp_achievement_belong_abu')
            ->leftJoin('myscrm_erp_user erpAchievementBelongAbuOwner', 'erpAchievementBelongAbuOwner.UserGUID= myscrm_payment_achievement.erp_achievement_belong_abu_owner')
            ->leftJoin('myscrm_erp_user erpAchievementBelongBUManager', 'erpAchievementBelongBUManager.UserGUID= myscrm_payment_achievement.erp_achievement_belong_bu_manager')
            ->leftJoin('myscrm_erp_business_unit erpAchievementBelongBu', 'erpAchievementBelongBu.BUGUID= myscrm_payment_achievement.erp_achievement_belong_bu')
            ->leftJoin('myscrm_receivable_item', 'myscrm_receivable_item.receivable_item_id= myscrm_payment_achievement.receivable_item_id')
            ->leftJoin('myscrm_money_category_item', 'myscrm_money_category_item.money_category_item_id= myscrm_payment_achievement.money_category_item_id')
            ->leftJoin('myscrm_contract_company', 'myscrm_contract_manage.company_id = myscrm_contract_company.company_id')
            ->leftJoin('myscrm_erp_business_unit contractArea', 'myscrm_contract_manage.area_id= contractArea.BUGUID')
            ->leftJoin('myscrm_contract_property contractProperty', 'myscrm_contract_manage.contract_property_id= contractProperty.property_id');

        // 搜索关键字
        self::setKeywordQuery($query, $keyword);

        if (!empty($areaId)) {//区域id
            $query->andWhere(['area_id' => $areaId]);
        }

        if (!empty($signPeopleId)) {//签约人
            $query->andWhere(['sign_people_id' => $signPeopleId]);
        }

        $query->orderBy(['contract_code' => SORT_ASC, 'myscrm_payment_achievement.created_on' => SORT_ASC]);
        $query->indexBy('achievement_id');
        return $query->asArray()->all();
    }

    /**
     * 导出合同回款业绩
     */
    public static function getContractDocumentListForExport($status, $property, $keyword, $areaId, $signPeopleId)
    {
        $query = ContractManageEntity::find()
            ->select("myscrm_contract_manage.contract_id,myscrm_contract_manage.customer_name,myscrm_contract_manage.customer_full_name,myscrm_contract_manage.contract_code"
                . ",myscrm_contract_document.*,documentKeeper.UserName as keeper_name"
                . ",contractArea.BUName as area_name");
        $query = self::setStatusCondition($status, $query);

        if (!empty($property)) {
            //合同性质
            $query->andWhere(['contract_property_id' => $property]);
        }

        $query->innerJoin('myscrm_contract_document', 'myscrm_contract_document.contract_id= myscrm_contract_manage.contract_id')
            ->leftJoin('myscrm_erp_user documentKeeper', 'documentKeeper.UserGUID = myscrm_contract_document.keeper')
            ->leftJoin('myscrm_contract_company', 'myscrm_contract_manage.company_id = myscrm_contract_company.company_id')
            ->leftJoin('myscrm_erp_business_unit contractArea', 'myscrm_contract_manage.area_id = contractArea.BUGUID');

        // 搜索关键字
        self::setKeywordQuery($query, $keyword);

        if (!empty($areaId)) {//区域id
            $query->andWhere(['area_id' => $areaId]);
        }

        if (!empty($signPeopleId)) {//签约人
            $query->andWhere(['sign_people_id' => $signPeopleId]);
        }

        $query->orderBy(['contract_code' => SORT_ASC, 'document_name' => SORT_ASC]);
        $query->indexBy('document_id');
        return $query->asArray()->all();
    }

    /**
     * 导出违规合同
     */
    public static function getContractViolatedListForExport($status, $property, $keyword, $areaId, $signPeopleId)
    {
        $query = ContractManageEntity::find()
            ->select("myscrm_contract_manage.contract_id,myscrm_contract_manage.customer_name,myscrm_contract_manage.customer_full_name,myscrm_contract_manage.contract_code"
                . ",myscrm_violated_contract.*,principal.UserName as principal_name,approver.UserName as approver_name"
                . ",myscrm_violated_category.category_name,myscrm_violated_littler_category.littler_category_name"
                . ",contractArea.BUName as area_name");
        $query = self::setStatusCondition($status, $query);

        if (!empty($property)) {
            //合同性质
            $query->andWhere(['contract_property_id' => $property]);
        }

        $query->innerJoin('myscrm_violated_contract', 'myscrm_violated_contract.contract_code= myscrm_contract_manage.contract_code')
            ->leftJoin('myscrm_erp_user principal', 'principal.userguid = myscrm_violated_contract.violated_principal')
            ->leftJoin('myscrm_erp_user approver', 'approver.userguid = myscrm_violated_contract.approver')
            ->leftJoin('myscrm_violated_category', 'myscrm_violated_category.violated_category_id = myscrm_violated_contract.violated_category_id')
            ->leftJoin('myscrm_violated_littler_category', 'myscrm_violated_littler_category.violated_littler_category_id = myscrm_violated_contract.violated_littler_category_id')
            ->leftJoin('myscrm_contract_company', 'myscrm_contract_manage.company_id = myscrm_contract_company.company_id')
            ->leftJoin('myscrm_erp_business_unit contractArea', 'myscrm_contract_manage.area_id= contractArea.BUGUID');

        // 搜索关键字
        self::setKeywordQuery($query, $keyword);

        if (!empty($areaId)) {//区域id
            $query->andWhere(['area_id' => $areaId]);
        }

        if (!empty($signPeopleId)) {//签约人
            $query->andWhere(['sign_people_id' => $signPeopleId]);
        }

        $query->orderBy(['myscrm_contract_manage.contract_code' => SORT_ASC]);
        $query->indexBy('violated_contract_id');
        return $query->asArray()->all();
    }

    /**
     * 导出特批发票
     */
    public static function getContractSpecialInvoiceListForExport($status, $property, $keyword, $areaId, $signPeopleId)
    {
        $query = ContractManageEntity::find()
            ->select("myscrm_contract_manage.contract_id,myscrm_contract_manage.customer_name,myscrm_contract_manage.customer_full_name,myscrm_contract_manage.contract_code"
                . ",myscrm_special_approval_invoice.*,principal.UserName as principal_name,myscrm_invoice.customer_name as invoice_customer_name,myscrm_invoice.invoice_date,"
                . ",invoiceCompany.BUName as invoice_company_name,myscrm_contract_company.company_name as contract_company_name"
                . ",contractArea.BUName as area_name");
        $query = self::setStatusCondition($status, $query);

        if (!empty($property)) {
            //合同性质
            $query->andWhere(['contract_property_id' => $property]);
        }

        $query->innerJoin('myscrm_invoice', 'myscrm_invoice.contract_id= myscrm_contract_manage.contract_id')
            ->innerJoin('myscrm_special_approval_invoice', 'myscrm_special_approval_invoice.invoice_no= myscrm_invoice.invoice_no')
            ->leftJoin('myscrm_invoice_business_unit invoiceCompany', 'invoiceCompany.BUGUID = myscrm_invoice.invoice_company_id')//开票公司
            ->leftJoin('myscrm_erp_user principal', 'principal.userguid = myscrm_special_approval_invoice.promise_fulfil_principle')
            ->leftJoin('myscrm_contract_company', 'myscrm_contract_manage.company_id = myscrm_contract_company.company_id')
            ->leftJoin('myscrm_erp_business_unit contractArea', 'myscrm_contract_manage.area_id= contractArea.BUGUID');

        // 搜索关键字
        self::setKeywordQuery($query, $keyword);

        if (!empty($areaId)) {//区域id
            $query->andWhere(['area_id' => $areaId]);
        }

        if (!empty($signPeopleId)) {//签约人
            $query->andWhere(['sign_people_id' => $signPeopleId]);
        }

        $query->orderBy(['myscrm_contract_manage.contract_code' => SORT_ASC]);
        $query->indexBy('special_approval_invoice_id');
        return $query->asArray()->all();
    }

    /**
     * 导出特批授权
     */
    public static function getContractSpecialAuthorizeListForExport($status, $property, $keyword, $areaId, $signPeopleId)
    {
        $contract_data = self::getContractSpecialAuthorizeList($status, $property, $keyword, $areaId, $signPeopleId);
        $user = \Yii::$app->user->identity;
        $userEntity = ErpInterfaceService::getUserByCode($user->usercode);
        #显示全部区域数据
        if ($userEntity->ViewAllArea) {
            $no_contract_data = self::getNoContractSpecialAuthorizeList();
        } else {
            $no_contract_data = [];
        }

        $result = [];
        $contract_id_list = [];
        foreach ($contract_data as $data) {
            $contract_id = $data['contract_id'];
            if (!empty($contract_id)) {
                if (in_array($contract_id, $contract_id_list)) {
                    continue;
                } else {
                    $contract_id_list[] = $contract_id;
                }
            }
            $result[] = $data;
        }
        foreach ($no_contract_data as $data) {
            $contract_id = $data['contract_id'];
            if (!empty($contract_id)) {
                if (in_array($contract_id, $contract_id_list)) {
                    continue;
                } else {
                    $contract_id_list[] = $contract_id;
                }
            }
            $result[] = $data;
        }

        return $result;
    }

    private static function getContractSpecialAuthorizeList($status, $property, $keyword, $areaId, $signPeopleId)
    {
        $query = ContractManageEntity::find()
            ->select("myscrm_contract_manage.contract_id,myscrm_contract_manage.customer_name,myscrm_contract_manage.customer_full_name,myscrm_contract_manage.contract_code"
                . ",myscrm_special_approval_authorize.*,principal.UserName as principal_name,"
                . ",myscrm_organization.UniqueName,myscrm_organization.FriendlyName"
                . ",contractArea.BUName as area_name");
        $query = self::setStatusCondition($status, $query);

        if (!empty($property)) {
            //合同性质
            $query->andWhere(['contract_property_id' => $property]);
        }

        $query->innerJoin('myscrm_special_approval_authorize', 'myscrm_special_approval_authorize.contract_code= myscrm_contract_manage.contract_code')
            ->leftJoin('myscrm_organization', 'myscrm_organization.Id = myscrm_special_approval_authorize.organization_id')
            ->leftJoin('myscrm_erp_user principal', 'principal.userguid = myscrm_special_approval_authorize.promise_fulfil_principle')
            ->leftJoin('myscrm_contract_company', 'myscrm_contract_manage.company_id = myscrm_contract_company.company_id')
            ->leftJoin('myscrm_erp_business_unit contractArea', 'myscrm_contract_manage.area_id= contractArea.BUGUID');

        // 搜索关键字
        self::setKeywordQuery($query, $keyword);

        if (!empty($areaId)) {//区域id
            $query->andWhere(['area_id' => $areaId]);
        }

        if (!empty($signPeopleId)) {//签约人
            $query->andWhere(['sign_people_id' => $signPeopleId]);
        }

        $query->orderBy(['myscrm_contract_manage.contract_code' => SORT_ASC]);
        $query->indexBy('special_approval_authorize_id');
        return $query->asArray()->all();

    }

    private static function getNoContractSpecialAuthorizeList()
    {
        $query = SpecialApprovalAuthorizeEntity::find()
            ->select("myscrm_contract_manage.contract_id,myscrm_contract_manage.customer_name,myscrm_contract_manage.customer_full_name,myscrm_contract_manage.contract_code"
                . ",myscrm_special_approval_authorize.*,principal.UserName as principal_name,"
                . ",myscrm_organization.UniqueName,myscrm_organization.FriendlyName"
                . ",contractArea.BUName as area_name");

        $query->leftJoin('myscrm_contract_manage', 'myscrm_special_approval_authorize.contract_code= myscrm_contract_manage.contract_code')
            ->leftJoin('myscrm_organization', 'myscrm_organization.Id = myscrm_special_approval_authorize.organization_id')
            ->leftJoin('myscrm_erp_user principal', 'principal.userguid = myscrm_special_approval_authorize.promise_fulfil_principle')
            ->leftJoin('myscrm_contract_company', 'myscrm_contract_manage.company_id = myscrm_contract_company.company_id')
            ->leftJoin('myscrm_erp_business_unit contractArea', 'myscrm_contract_manage.area_id= contractArea.BUGUID');

        $query->orderBy(['myscrm_special_approval_authorize.promise_fulfil_time' => SORT_ASC]);
        $query->indexBy('special_approval_authorize_id');
        return $query->asArray()->all();
    }

    /**
     * 获取租期即将到期授权
     * @param $expiredDate 到期日
     * @return array|\yii\db\ActiveRecord[]
     */
    public static function getAuthorizationExpiredRemind($expiredDate)
    {
        $query = ContractManageEntity::find()
            ->select('a.PartyA,a.BeginOn,a.EndOn,c.UniqueName,c.FriendlyName,a.ContractId,myscrm_contract_manage.*')
            ->innerJoin('myscrm_contract a',
                "DATE_FORMAT(a.endon,'%Y-%m-%d') = date(:expireDate) and
                a.Status = 1 and
                a.ContractNum = myscrm_contract_manage.contract_code", [':expireDate' => $expiredDate])
            ->innerJoin('myscrm_organization c', 'a.OrganizationId = c.id')
            ->asArray()
            ->all();

        return $query;
    }

    /**
     * 获取合同签约应用
     * @param $contractId 合同id
     */
    public static function getContractApps($contractId)
    {
        $query = ContractManageEntity::getDb()
            ->createCommand("select a.FriendlyName,b.TotalNum
            from myscrm_application a,myscrm_organizationToAuthorize  b
            where a.ApplicationId=b.ApplicationId
            and b.ContractId=:contractId and b.TotalNum>0", [':contractId' => $contractId])
            ->queryAll();
        return $query;
    }

    /**
     * erp获取合同信息
     * @param string $propertyId 合同性质ID
     * @param date|null $startReviewDate 开始审核日期（date|null:date:给定日期以后审核的数据，null：所有审核的合同数据）
     * @param int $page 取数页码,default=1
     * @param int $pageSize 分页大小,default=20
     * @return array|\yii\db\ActiveRecord[]
     */
    public static function getContractsForERP($propertyId, $startReviewDate, $page = 1, $pageSize = 20)
    {
        $page = empty($page) ? 1 : $page;
        $pageSize = empty($pageSize) ? 20 : $pageSize;

        $where = 'contract_status = 2';
        $params = [];

        if (!empty($propertyId)) {
            $where .= ' and contract_property_id = :propertyId ';
            $params[':propertyId'] = $propertyId;
        }

        if (!empty($startReviewDate)) {
            $where .= ' and review_date >= :reviewDate ';
            $params[':reviewDate'] = $startReviewDate;
        }
        $query = ContractManageEntity::find()
            ->select('contract_id,contract_code,customer_id,customer_name,top_customer_name,customer_full_name,sign_amount,
            give_work_day,develop_work_day,implement_work_day,a.BeginOn as begin_on,a.EndOn as end_on')
            ->leftJoin('myscrm_contract a', 'a.contractNum = myscrm_contract_manage.contract_code')
            ->where($where, $params)
            ->orderBy('review_date DESC,modified_on DESC')
            ->offset(($page - 1) * $pageSize)
            ->limit($pageSize)
            ->asArray()
            ->all();

        return $query;
    }

    /**
     * 根据时间获取合同信息
     * @param type $beginTime
     * @param type $endTime
     * @return type
     */
    public static function getContractListByTime($beginTime, $endTime,$contractid='')
    {
        $sql = "select m.contract_id as ContractGUID, m.contract_code as ContractCode, c.category_name as ContractType, bu.BUName as BelongArea, m.customer_id as CustomerGUID,m.customer_full_name as QyCstName,m.erp_contract_code as ErpContractCode,
                '云链' as ContractOrigin, m.sign_amount as SignMoney, u.UserGUID as SignUserGUID, m.sign_date as SignDate,m.input_date as EntryDate, 1 as ContractQC, m.balance_amount as JsMoney, m.balance_amount as jsAmount,m.effect_date as Sxdate,
                m.contract_property_id as ContractPropertyId, p.property_name as ContractPropertyName, m.top_customer_name as TopCustomerName,
                case m.business_type_id
                when 'f85342fe-54ca-11e7-9f96-000c29310f8b' THEN m.supplier_name
                when 'f85729eb-54ca-11e7-9f96-000c29310f8b' THEN m.supplier_name
                when 'f854fa59-54ca-11e7-9f96-000c29310f8b' THEN m.activity_name
                when 'f851bc86-54ca-11e7-9f96-000c29310f8b' THEN m.supplier_name
                ELSE m.customer_full_name end as CustomerFullName,
                c.category_name as CategoryName, b.big_category_name as BigCategoryName, l.littler_category_name as LittlerCategoryName, m.rent_amount as RentAmount, m.delivery_amount as DeliveryAmount,
                m.application_deal_amount as ApplicationDealAmount, m.draw_project_numeric as DrawProjectNumeric, m.back_amount as BackAmount, m.back_amount as fkAmount,m.income_amount as IncomeAmount, m.attribute_name as AttributeName,
                m.repayment_type as RepaymentType, m.repayment_sure as RepaymentSure, m.repayment_company as RepaymentCompany,
                cp.EstablishTime,cc.company_name as ContractLyQy,bu.BUGUID,m.erp_contract_id ERPContractGUID,m.balance_should_amount YjsAmount
                from myscrm_contract_manage as m
                left join myscrm_contract_property as p on m.contract_property_id=p.property_id
                left join myscrm_contract_category as c on c.category_id=m.category_id
                left join myscrm_contract_big_category as b on b.big_category_id=m.big_category_id
                left join myscrm_contract_littler_category as l on l.littler_category_id=m.littler_category_id
                left join myscrm_erp_user as u on m.sign_people_id=u.UserGUID
                left join myscrm_erp_business_unit as bu on bu.BUGUID=m.area_id
                left join myscrm_contract_company as cc on m.company_id = cc.company_id
                left join (
                    SELECT contract_id,MIN(authorize_time) EstablishTime FROM myscrm_contract_payover group by contract_id
                ) cp on cp.contract_id=m.contract_id
                where m.is_cancelled = 0 and m.contract_status > 0 ";

        if (!empty($beginTime) && !empty($endTime)) {
            $sql .= " and input_date>='".$beginTime."' and input_date<='".$endTime."' ";
        }
        if(!empty($contractid)){
            $sql .= " and m.contract_id='".$contractid."'";
        }
        $data = ContractManageEntity::getDb()->createCommand($sql)->queryAll();

        return $data;
    }

    public static function getContractFkList($contractid='')
    {
        $sql = "select contract_code as ContractCode, back_status as Isfk,
                back_date as fkDate, back_amount as fkAmount
                from myscrm_contract_manage
                where is_cancelled = 0 and contract_status > 0";

        if(!empty($contractid)){
            $sql .= " and contract_id='".$contractid."'";
        }

        $data = ContractManageEntity::getDb()->createCommand($sql)->queryAll();

        return $data;
    }

    /**
     * 根据时间获取合同信息
     * @param $beginTime
     * @param $endTime
     * @return array
     */
    public static function getContractListByTimeNoAsFields($beginTime, $endTime)
    {
        $sql = "select m.contract_id as ContractGUID,m.contract_code as ContractCode, m.customer_name, m.customer_full_name, p.property_name, c.category_name, b.big_category_name, " .
            "l.littler_category_name,m.input_date, m.sign_date, bu.BUName, m.sign_amount, m.contract_id, u.UserName " .
            "from myscrm_contract_manage as m " .
            "left join myscrm_contract_property as p on m.contract_property_id=p.property_id " .
            "left join myscrm_contract_category as c on c.category_id=m.category_id " .
            "left join myscrm_contract_big_category as b on b.big_category_id=m.big_category_id " .
            "left join myscrm_contract_littler_category as l on l.littler_category_id=m.littler_category_id " .
            "left join myscrm_erp_user as u on m.sign_people_id=u.UserGUID " .
            "left join myscrm_erp_business_unit as bu on bu.BUGUID=m.area_id where m.is_cancelled=0 and m.contract_status > 0 ";

        if (!empty($beginTime) && !empty($endTime)) {
            $sql .= "and input_date>=:beginTime and input_date<=:endTime";
        }

        $data = ContractManageEntity::getDb()->createCommand($sql, [':beginTime' => $beginTime, ':endTime' => $endTime])->queryAll();

        foreach ($data as $k => $v) {
            $application = ContractApplicationRepository::getAllContractApplicationByContractId($v['contract_id']);
            $data[$k]['application_name'] = $str = "";
            if (!empty($application)) {
                foreach ($application as $key => $val) {
                    if (!empty($val['application_name'])) {
                        $str .= (empty($str) ? $val['application_name'] : "," . $val['application_name']);
                    }
                }
                $data[$k]['application_name'] = $str;
            }
        }
        return $data;
    }

    /**
     * 根据时间获取签约业绩
     * @param type $beginTime
     * @param type $endTime
     * @return type
     */
    public static function getSignAchievementsByTime($beginTime, $endTime)
    {
        $sql = "select myscrm_sign_achievement.contract_id, myscrm_contract_manage.contract_code, myscrm_contract_littler_category.littler_category_name, myscrm_sign_achievement.achievement_amount, myscrm_sign_achievement.achievement_belong_time " .
            "from myscrm_sign_achievement inner join myscrm_contract_manage on myscrm_contract_manage.contract_id = myscrm_sign_achievement.contract_id " .
            "left join myscrm_contract_littler_category on myscrm_contract_manage.littler_category_id = myscrm_contract_littler_category.littler_category_id" .
            "where myscrm_contract_manage.is_cancelled = 0 and myscrm_contract_manage.contract_status > 0 ";
        if (!empty($beginTime) && !empty($endTime)) {
            $sql .= " and achievement_belong_time >= :beginTime and achievement_belong_time <= :endTime";
        }

        $data = ContractManageEntity::getDb()->createCommand($sql, [':beginTime' => $beginTime, ':endTime' => $endTime])->queryAll();
        return $data;
    }

    /**
     * 根据时间获取回款业绩
     * @param type $beginTime
     * @param type $endTime
     * @return type
     */
    public static function getPaymentInfoByTime($beginTime, $endTime,$contractid='')
    {
        $sql = "select myscrm_contract_payment.input_date as KxgdDate, myscrm_contract_payment.payment_id as ReceivedGUID, myscrm_contract_payment.payment_date as ReceivedDate, (myscrm_contract_payment.payment_amount-myscrm_contract_payment.withdraw_fee) as ReceivedMoney," .
            "myscrm_contract_payment.contract_id as ContractGUID,myscrm_contract_payment.finance_code as AutoCode, myscrm_contract_payment.payment_account_name as PayCstName " .
            "from myscrm_contract_payment inner join myscrm_contract_manage on myscrm_contract_manage.contract_id = myscrm_contract_payment.contract_id " .
            "where myscrm_contract_manage.is_cancelled = 0 and myscrm_contract_manage.contract_status > 0 ";
        if (!empty($beginTime) && !empty($endTime)) {
            $sql .= " and payment_date >= '".$beginTime."' and payment_date <= '".$endTime."'";
        }
        if(!empty($contractid)){
            $sql .= " and myscrm_contract_manage.contract_id='".$contractid."'";
        }

        $data = ContractManageEntity::getDb()->createCommand($sql)->queryAll();
        return $data;
    }

    /**
     * 根据时间获取租户信息
     * @param type $beginTime
     * @param type $endTime
     * @return type
     */
    public static function getProductInfoByTime($beginTime, $endTime,$contractid='')
    {
        $sql = "select myscrm_contract_application.contract_application_id as ProductGUID, myscrm_application_info.application_name as ProductName, myscrm_contract_application.contract_id as ContractGUID," .
            "myscrm_application_info.application_id as ProductInfoID,myscrm_contract_application.authorization_numeric as Quantity,myscrm_contract_application.rent_period as Rent_Time " .
            "from myscrm_contract_application inner join myscrm_contract_manage on myscrm_contract_manage.contract_id = myscrm_contract_application.contract_id " .
            "left join myscrm_application_info on myscrm_contract_application.application_id = myscrm_application_info.application_id " .
            "where myscrm_contract_manage.is_cancelled = 0 and myscrm_contract_manage.contract_status > 0 ";

        if (!empty($beginTime) && !empty($endTime)) {
            $sql .= " and myscrm_contract_application.created_on >= '".$beginTime."' and myscrm_contract_application.created_on <= '".$endTime."'";
        }
        if(!empty($contractid)){
            $sql .= " and myscrm_contract_manage.contract_id='".$contractid."'";
        }

        $data = ContractManageEntity::getDb()->createCommand($sql)->queryAll();
        return $data;
    }

    /**
     * 根据时间获取发票信息
     * @param type $beginTime
     * @param type $endTime
     * @return type
     */
    public static function getInvoicesByTime($beginTime, $endTime,$contractid='')
    {
        $sql = "SELECT
                myscrm_invoice.invoice_id AS ReceiptGUID,
                myscrm_invoice.invoice_no AS ReceiptCode,
                IF (myscrm_invoice.invoice_category = 0,'销售票','服务票') AS ReceiptType,
                myscrm_invoice.invoice_amount AS ReceiptMoney,
                myscrm_invoice_business_unit.BUName AS ReceiptCompany,
                myscrm_invoice.invoice_date AS ReceiptDate,
                myscrm_invoice.red_by_invoice_id,
                myscrm_invoice.is_red_invoice,
                myscrm_invoice.is_receipt,
                myscrm_invoice.invoice_code FpNum,
                myscrm_invoice.is_lose,
                IF(myscrm_invoice.is_lose=1,'是','否') IsYs,
                myscrm_invoice.contract_id ContractGuid,
                IF(myscrm_invoice.is_red_invoice=1,'是','否') IsHc,
                myscrm_invoice.red_by_invoice_id HcReceiptGuid,
                myscrm_invoice.customer_name CstName,
                myscrm_invoice.proposer_id SqrUser,
                myscrm_invoice.invoice_date CreateDate,
                IF(myscrm_invoice.invoice_type=1,'增值税专用发票','增值税普通发票') FpLx,
                IF(IFNULL(myscrm_invoice.contract_id,'')='','0','1') IsRelaToContract,
                myscrm_invoice.remark Bz
                FROM myscrm_invoice
                LEFT JOIN myscrm_contract_manage ON myscrm_contract_manage.contract_id = myscrm_invoice.contract_id AND myscrm_contract_manage.is_cancelled = 0 AND myscrm_contract_manage.contract_status > 0
                LEFT JOIN myscrm_invoice_business_unit ON myscrm_invoice_business_unit.BUGUID = myscrm_invoice.invoice_company_id
                left join myscrm_erp_user u on myscrm_invoice.proposer_id=u.UserGUID
                WHERE myscrm_invoice.invoice_id is not null ";
        if (!empty($beginTime) && !empty($endTime)) {
            $sql .= " and myscrm_invoice.invoice_date >= '".$beginTime."' and myscrm_invoice.invoice_date <= '".$endTime."'";
        }
        if(!empty($contractid)){
            $sql .= " and myscrm_contract_manage.contract_id='".$contractid."'";
        }

        $data = ContractManageEntity::getDb()->createCommand($sql)->queryAll();
        return $data;
    }

    /**
     * 获取所有发票摘要数据
     */
    public static function getInvoicesAbstract(){
        $sql = "SELECT
                summary_id,
                invoice_id Receiptguid,
                summary_type Abstract,
                summary_amount Amount
                FROM myscrm_invoice_summary";

        $data = ContractManageEntity::getDb()->createCommand($sql)->queryAll();
        return $data;
    }

    /**
     * 根据时间获取租户信息
     * @param type $beginTime
     * @param type $endTime
     * @return type
     */
    public static function getGetOrganizationInfoByTime($beginTime, $endTime)
    {
        $sql = "select myscrm_contract.ContractId as DeliveryGUID, myscrm_contract.ContractNum as DeliveryCode, myscrm_contract.BeginOn as DeliveryDate," .
            "myscrm_contract.EndOn as EndDate, myscrm_contract.contractId as   " .
            "from myscrm_contract inner join myscrm_contract_manage on myscrm_contract_manage.contract_code = myscrm_contract.ContractNum " .
            "left join myscrm_organization on myscrm_contract.OrganizationId = myscrm_organization.Id " .
            "where myscrm_contract_manage.is_cancelled = 0 and myscrm_contract_manage.contract_status > 0 ";
        if (!empty($beginTime) && !empty($endTime)) {
            $sql .= " and BeginOn >= :beginTime and EndOn <= :endTime";
        }

        $data = ContractManageEntity::getDb()->createCommand($sql, [':beginTime' => $beginTime, ':endTime' => $endTime])->queryAll();
        return $data;
    }

    /**
     * 款项发票回款关联表
     * @param type $beginTime
     * @param type $endTime
     * @return type
     */
    public static function getInvoicePaymentByTime($beginTime, $endTime,$contractid='')
    {
        $sql = 'SELECT
                        myscrm_contract_receivable.contract_id AS ContractGUID,
                        myscrm_contract_payment.payment_id AS HKGUID,
                        myscrm_invoice_payment.cur_payment_amount AS FactMoney,
                        myscrm_invoice_payment.invoice_payment_id AS KXFPHKRelaGUID,
                        myscrm_invoice_receivable.receivable_id AS KXGUID,
                        myscrm_invoice_receivable.invoice_id AS KPGUID,
                        myscrm_invoice_receivable.cur_billing_amount AS KPAmount
                FROM myscrm_contract_manage
                inner join myscrm_contract_receivable on myscrm_contract_manage.contract_id=myscrm_contract_receivable.contract_id
                inner join myscrm_invoice_receivable on myscrm_contract_receivable.receivable_id=myscrm_invoice_receivable.receivable_id
                inner join myscrm_invoice on myscrm_invoice.invoice_id=myscrm_invoice_receivable.invoice_id
                left join myscrm_invoice_payment on myscrm_invoice_payment.invoice_receivable_id=myscrm_invoice_receivable.invoice_receivable_id
                left join myscrm_contract_payment on myscrm_contract_payment.payment_id=myscrm_invoice_payment.payment_id
                WHERE myscrm_contract_manage.is_cancelled = 0 and myscrm_contract_manage.contract_status > 0 ';

        if (!empty($beginTime) && !empty($endTime)) {
            $sql .= " and myscrm_invoice.invoice_date >= '".$beginTime."' and myscrm_invoice.invoice_date <= '".$endTime."'";
        }
        if(!empty($contractid)){
            $sql .= " and myscrm_contract_manage.contract_id='".$contractid."'";
        }

        return ContractManageEntity::getDb()->createCommand($sql)->queryAll();
    }

    /**
     * 获取推送需要的应收款数据
     * @param $contractguid
     * @return array
     */
    public static function getPushEkpReceivableData($contractguid){
        $hostpath = 'http://' . $_SERVER['HTTP_HOST'];
        $con = ConfigEntity::getDb();
        $sql = "SELECT
                        IFNULL(m.erp_contract_id,a.contract_id) contract_id,
                        a.application_id product_id,
                        i.application_name product_name,
                        IF(po.nums is null,'部分授权',IF(a.authorization_numeric=po.nums,'全部授权','部分授权')) product_state,
                        if(po.nums is null,0,po.nums) number,
                        CONCAT(:hostpath,'/contract/edit.shtml#/dredge?id=',a.contract_id,'&contractType=%E5%BC%80%E5%8F%91%E5%95%86SaaS%E4%BA%A7%E5%93%81&resource=ekp') url
                        FROM myscrm_contract_application a
                        inner join myscrm_contract_manage m on m.contract_id=a.contract_id
                        inner join myscrm_application_info i on a.application_id=i.application_id
                        left join (
                            SELECT contract_id,application_name,SUM(authorization_numeric) nums FROM myscrm_contract_payover where contract_id=:contract_id
                            group by application_name
                        ) po on a.contract_id=po.contract_id AND i.application_name=po.application_name
                        WHERE po.nums>0 AND a.contract_id=:contract_id";
        return $con->createCommand($sql,[':contract_id' => $contractguid,':hostpath' => $hostpath])->queryAll();
    }

    /**
     * 款项发票回款关联表
     * @param type $beginTime
     * @param type $endTime
     * @return type
     */
    public static function getReceivableInfoByTime($contractid ='',$beginTime, $endTime)
    {
        $sql = "SELECT
                    myscrm_contract_receivable.receivable_id AS ShouldGetGUID,
                    myscrm_contract_receivable.is_operation AS IsOperation,
                    myscrm_contract_receivable.is_bad_debt AS IsBadDebt,
                    myscrm_contract_receivable.contract_id AS ContractGUID,
                    myscrm_receivable_item.receivable_item_name AS IteamName,
                    myscrm_money_category_item.money_category_item_name AS IteamType,
                    myscrm_contract_receivable.payee_id AS GetIner,
                    myscrm_contract_receivable.receivable_date AS GetDate,
                    myscrm_contract_receivable.receivable_amount AS GetMoney,
                    myscrm_contract_receivable.payment_condition AS PayCondition,
                    myscrm_buss_account_types.finance_type_name AS GetType,
                    myscrm_contract_receivable.contract_property AS ReceivableType,
					case when myscrm_contract_receivable.contract_property=1 then '续签' else '新增' end ReceivableTypeCh,
                    myscrm_buss_account_types.type_name as IncomeType,
                    myscrm_contract_manage.balance_should_amount js_amount,
                    myscrm_contract_receivable.remark
                FROM myscrm_contract_receivable
                LEFT JOIN myscrm_contract_manage ON myscrm_contract_manage.contract_id = myscrm_contract_receivable.contract_id
                LEFT JOIN myscrm_receivable_item ON myscrm_receivable_item.receivable_item_id = myscrm_contract_receivable.receivable_item_id
                LEFT JOIN myscrm_money_category_item ON myscrm_money_category_item.money_category_item_id = myscrm_contract_receivable.money_category_item_id
                LEFT JOIN myscrm_buss_account_types ON myscrm_buss_account_types.type_id = myscrm_contract_receivable.revenue_type
                WHERE myscrm_contract_manage.is_cancelled = 0 AND myscrm_contract_manage.contract_status > 0";

        if (!empty($beginTime) && !empty($endTime)) {
            $sql .= " and IFNULL(myscrm_contract_receivable.receivable_date,NOW()) >= '".$beginTime."' and IFNULL(myscrm_contract_receivable.receivable_date,NOW()) <= '".$endTime."'";
        }
        if(!empty($contractid) && $contractid !=''){
            $sql .= " AND myscrm_contract_receivable.contract_id='".$contractid."'";
        }
        $data = ContractManageEntity::getDb()->createCommand($sql)->queryAll();

        return $data;
    }

    public static function getContractApplicationList($code)
    {
        $sql = "SELECT
                    c.contract_id,
                    contract_code,
                    sign_date,
                    ca.contract_application_id,
                    a.application_id,
                    a.unique_name,
                    a.application_name,
                    ca.unit,
                    ca.price,
                    ca.authorization_numeric
                FROM
                    myscrm_contract_manage c
                LEFT JOIN myscrm_contract_application ca ON c.contract_id = ca.contract_id
                LEFT JOIN myscrm_application_info a ON ca.application_id = a.application_id
                WHERE
                contract_status > 0  ";
        $params = [];
        if (!empty($code)) {
            $sql .= " and contract_code like :contract_code";
            $params[':contract_code'] = '%' . $code . '%';
        }

        return ContractManageEntity::getDb()->createCommand($sql, $params)->queryAll();
    }

    //保存特批授权数据
    public static function saveSpecialApproval($contract,$contractentity){
        $code = $contract->contract_code;
        $authid = $contract->auth_id;
        $specal = SpecialApprovalAuthorizeEntity::find()->where(['auth_id'=>$authid])->one();
        if(empty($specal)){
            $specal = new SpecialApprovalAuthorizeEntity();
            $specal->created_on = ConfigEntity::getDbNowDate();
        }else{
            $specal->modified_on = ConfigEntity::getDbNowDate();
        }

        $specialType = [
            '合同未归档'=>'1',
            '首期款未回'=>'2',
            '授权信息与合同信息不一致'=>'3',
            '合同扫描件未提供'=>'4',
            '结算款未支付'=>'5',
            '其他'=>'6',
            '合同未归档，首期款未回'=>'7'
        ];

        $specal->special_approval_authorize_id = StringHelper::uuid();
        $specal->auth_id = $authid;
        $specal->organization_name = $contract->special_approval_tenant_name;
        $specal->contract_code = $code;
        $specal->category = $specialType[$contract->special_approval_type];
        $specal->promise_fulfil_time = $contract->promise_online;
        $specal->actual_fulfil_time = $contract->actual_online;
        $specal->promise_fulfil_principle = empty($contract->special_approval_user_id)?empty($contractentity)?'':$contractentity->sign_people_id:$contract->special_approval_user_id;

        $user = ErpUserRepository::getBuguidByUserId($specal->promise_fulfil_principle);
        $specal->promise_fulfil_areaid = $user->BUGUID;

        $specal->comment = $contract->special_approval_remark;
        try {
            $ret = $specal->save();
        }catch (Exception $ex){
            $ret = false;
        }
        return $ret;
    }

    //保存交付信息
    public static function updateContractApplicationRent($contract,$contractid)
    {
        $code = $contract->contract_code;
        $auth_id = $contract->auth_id;
        $stock_out_id = $contract->stock_out_id;
        $stock_out_code = "";
        if(!empty($stock_out_id)){
            //读取出库单code
            $erpinterface = new ErpInterfaceService();
            $outStorageInfo = $erpinterface->GetDeliveryDetail($stock_out_id);
            $stock_out_code = $outStorageInfo['data']->DeliveryForm->Delivery_Code;
        }
        $application_list = $contract->application_list;
        $PayOverInfos = ContractPayoverEntity::find()->where(['contract_id'=>$contractid,'auth_id'=>$auth_id])->all();
        foreach($application_list as $item)
        {
            $isexists = false;
            $authappid = $item->auth_app_id;

            foreach($PayOverInfos as $payover){
                if($authappid == $payover->foreign_id){
                    $isexists = true;
                    //存在则更新
                    $payover->system_flag = $contract->system_flag;
                    $payover->tenant_id = $contract->tenant_id;
                    $payover->tenant_name = $contract->tenant_name;
                    $payover->tenant_code = $contract->tenant_code;
                    $payover->application_name = $item->application_name;
                    $payover->application_id = empty($item->application_id)?'':$item->application_id;
                    $payover->authorize_period = empty($item->application_period)?0:$item->application_period;
                    $payover->authorization_numeric = $item->application_numeric;
                    $payover->authorize_time = empty($item->application_time)?null:$item->application_time;
                    $payover->begin_time = empty($item->begin_time)?null:$item->begin_time;
                    $payover->end_time = empty($item->end_time)?null:$item->end_time;
                    $payover->stock_out_id = $contract->stock_out_id;
                    $payover->stock_out_code = $stock_out_code;
                    if(!$payover->save()){
                        return false;
                    }
                }
            }
            //不存在直接插入
            if(!$isexists){
                $newpayover = new ContractPayoverEntity();
                $newpayover->contract_payover_id = StringHelper::uuid();
                $newpayover->foreign_id = $authappid;
                $newpayover->contract_id = $contractid;
                $newpayover->contract_code = $code;
                $newpayover->auth_id = $contract->auth_id;
                $newpayover->system_flag = $contract->system_flag;
                $newpayover->tenant_id = $contract->tenant_id;
                $newpayover->tenant_name = $contract->tenant_name;
                $newpayover->tenant_code = $contract->tenant_code;
                $newpayover->edit_mode = 2; //刚过来的数据默认是编辑状态
                $newpayover->application_name = $item->application_name;
                $newpayover->application_id = empty($item->application_id)?'':$item->application_id;
                $newpayover->authorize_period = empty($item->application_period)?0:$item->application_period;
                $newpayover->authorization_numeric = $item->application_numeric;
                $newpayover->authorize_time = empty($item->application_time)?null:$item->application_time;
                $newpayover->begin_time = empty($item->begin_time)?null:$item->begin_time;
                $newpayover->end_time = empty($item->end_time)?null:$item->end_time;
                $newpayover->stock_out_id = $contract->stock_out_id;
                $newpayover->stock_out_code = $stock_out_code;
                if(!$newpayover->save()){
                    return false;
                }
            }

            //保存租户与合同关联关系
            $relationin = new ContractTenantRelationEntity();
            $relationin->contract_code = $code;
            $relationin->contract_id = $contractid;
            $relationin->tenant_id = $contract->tenant_id;
            $relationin->tenant_code = $contract->tenant_code;
            $relationin->tenant_name = $contract->tenant_name;
            $relationin->kt_date = empty($item->application_time)?null:$item->application_time;
            $relationin->system_flag = $contract->system_flag;
            $newser = new ContractService();
            $result = $newser->SetContractTenantRelation($relationin);
            if(!$result){
                return false;
            }
        }

        //已删除的同步删除
        foreach($PayOverInfos as $payover){
            //已经结转的数据不允许删除
            $isHaveCarryOver = ContractRevenueRepository::isHaveCarryover($payover->contract_id,$payover->contract_payover_id);
            if($isHaveCarryOver){
                continue;
            }

            $isexists = false;
            foreach($application_list as $item){
                if($item->auth_app_id == $payover->foreign_id){
                    $isexists = true;
                }
            }
            if(!$isexists){
                //曾经出过库,需同时更新或删除相关信息
                if(!empty($payover->shipment_id)){
                    //删除或更新出库信息
                    $shipment = ContractShipmentEntity::find()->where(['shipment_id'=>$payover->shipment_id])->one();
                    if($shipment->amount == $payover->shipment_old_amount){
                        $shipment->delete();
                    }else{
                        $shipment->amount = $shipment->amount - $payover->shipment_old_amount;
                        $shipment->shipment_numberic = $shipment->shipment_numberic - $payover->shipment_old_numberic;
                        $shipment->save();
                    };
                    //更新合同结算金额等信息
                    $mycontract = ContractManageEntity::find()->where(['contract_id'=>$contractid])->one();
                    $mycontract->balance_amount = $mycontract->balance_amount - $payover->shipment_old_amount;
                    $balanceAmount = $mycontract->balance_amount;
                    $balanceShouldAmount = $mycontract->balance_should_amount;
                    if($balanceAmount<=0){
                        $balanceStatus = 0;     //未结算
                    }else{
                        if($balanceAmount >= $balanceShouldAmount){
                            $balanceStatus = 1; //已结算
                        }else{
                            $balanceStatus = 2; //部分结算
                        }
                    };
                    $mycontract->back_status = $balanceStatus;
                    $mycontract->save();
                };

                $payover->delete();
            }
        }
        return true;
    }


    public static function getContractListByCustomerId($customer_id)
    {
        $sql = 'SELECT
                    contract.contract_id,
                    contract.contract_code,
                    contract.sign_date,
                    contract.sign_amount,
                    contract.area_id company_id,
                    contract.customer_name,
                    contract.customer_full_name,
                    u.BUName company_name
                FROM myscrm_contract_manage contract
                LEFT JOIN myscrm_contract_company company ON contract.company_id = company.company_id
				LEFT JOIN myscrm_erp_business_unit u on contract.area_id=u.BUGUID
                WHERE contract_status = 2 AND customer_id = :customer_id
                ORDER BY sign_date DESC
                LIMIT 20';

        $params = [];
        $params[':customer_id'] = $customer_id;

        return ContractManageEntity::getDb()->createCommand($sql, $params)->queryAll();
    }

    public static function getContractListByContractCode($keyword){
        $data = ContractManageEntity::find()
            ->select('contract_code')
            ->where(['contract_status'=>2,'is_cancelled'=>0,'business_type_id'=>'f85b3855-54ca-11e7-9f96-000c29310f8b'])
            ->andWhere("contract_code like '".$keyword."%'")
            ->limit(20)
            ->all();
        return $data;
    }

    public static function getContractByContractCode($contractcode){
        $sql = 'SELECT
                    contract.contract_id,
                    contract.contract_code,
                    contract.sign_date,
                    contract.sign_amount,
                    contract.area_id company_id,
                    contract.customer_id,
                    contract.customer_name,
                    contract.customer_full_name,
                    u.BUName company_name
                FROM myscrm_contract_manage contract
                LEFT JOIN myscrm_contract_company company ON contract.company_id = company.company_id
				LEFT JOIN myscrm_erp_business_unit u on contract.area_id=u.BUGUID
                WHERE contract_status = 2 AND contract_code = :contract_code
                ORDER BY sign_date DESC
                LIMIT 20';

        $params = [];
        $params[':contract_code'] = $contractcode;

        return ContractManageEntity::getDb()->createCommand($sql, $params)->queryAll();
    }

    /**
     * Method getContractInfoByContractid
     * 根据合同id获取合同信息
     *
     * @param string $contract_id
     *
     * @return array|null|\yii\db\ActiveRecord
     */
    public static function getContractInfoByContractid($contract_id = '')
    {
        if (empty($contract_id)) {
            return [];
        }

        //return ContractManageEntity::find()->select('*')->leftJoin('myscrm_contract_business_type', 'myscrm_contract_business_type.business_type_id = myscrm_contract_manage.business_type')->where(['contract_id' => $contract_id])->asArray()->one();
        return ContractManageEntity::find()->select('*')->where(['contract_id' => $contract_id])->asArray()->one();
    }

    /**
     * @param string $contract_code
     * @param string $fields
     * @return array|null|\yii\db\ActiveRecord
     */
    public static function getContractInfoByContractCode($contract_code = '',$fields = '*')
    {
        if (empty($contract_code)) {
            return [];
        }

        //return ContractManageEntity::find()->select('*')->leftJoin('myscrm_contract_business_type', 'myscrm_contract_business_type.business_type_id = myscrm_contract_manage.business_type')->where(['contract_id' => $contract_id])->asArray()->one();
        return ContractManageEntity::find()->select($fields)->where(['contract_code' => $contract_code])->asArray()->one();
    }


    /**
     * 根据合同id获取合同信息
     *
     * @param string $erp_contract_id
     *
     * @return array|null|\yii\db\ActiveRecord
     */
    public static function getContractInfoByLinkErpId($erp_contract_id = '')
    {
        if (empty($erp_contract_id)) {
            return [];
        }
        return ContractManageEntity::find()->select('*')->where(['contract_status'=>'2','erp_contract_id' => $erp_contract_id])->asArray()->one();
    }

    /**
     * 查询釆宝类合同,按月
     * @param $month
     */
    public static function getContractBySuppliername($suppliername){
        $contract = ContractManageEntity::find()->where(" business_type_id='f851bc86-54ca-11e7-9f96-000c29310f8b' and supplier_name = '".$suppliername."'")->one();
        return $contract;
    }

    /**
     * 根据供应商金牌信息查看是否已经有对应的合同
     * @param $supplierid
     * @param $bgtime
     * @param $endtime
     */
    public static function getContractBySupplierInfo($supplierid,$bgtime,$endtime){
        $sql = "SELECT m.contract_id FROM myscrm_contract_payover p
                inner join myscrm_contract_manage m on p.contract_id=m.contract_id
                WHERE m.supplier_id='".$supplierid."' AND DATE(p.begin_time)='".$bgtime."' AND DATE(p.end_time)='".$endtime."'";

        $manage = ContractManageEntity::getDb()->createCommand($sql)->queryOne();
        return $manage;
    }

    /**
     * @param $name
     */
    public static function getErpUserInfoByName($name){
        $data = ErpUserEntity::find()
            ->select('UserGUID,UserName')
            ->where(['like','UserName',$name.'%',false])
            ->limit(100)
            ->all();
        return $data;
    }

    /**
     * @param $name
     */
    public static function getContractByErpContractId($erpid){
        $data = ContractManageEntity::find()
            ->select('*')
            ->where(['erp_contract_id'=>$erpid])
            ->asArray()
            ->one();
        return $data;
    }

    public static function getContractOrderInfo($contractguid,$beginTime,$endTime){
        $sql = "SELECT
            application.contract_application_id OrderGUID,
            payover.contract_id ContractGUID,
            payover.application_id ProductGUID,
            payover.application_name ProductName,
            payover.authorize_time RentTime,
            payover.authorization_numeric Num,
            payover.authorize_period RentPeriod,
            payover.balance_type,
            payover.balance_amount JsAmount,
            payover.stock_out_code DeliveryCode,
            payover.stock_out_id DeliveryGUID,
            payover.authorize_time as begin_time,
            date_add(payover.authorize_time, interval payover.authorize_period month) as end_time,
            payover.created_on CreatedOn
        FROM
            myscrm_contract_payover payover
        LEFT JOIN myscrm_contract_application application ON (application.application_id = payover.application_id
        AND application.contract_id = payover.contract_id) WHERE application.contract_application_id is not null";
        $params = [];
        if(!empty($contractguid)){
            $sql = $sql." and payover.contract_id=:contract_id";
            $params[":contract_id"]=$contractguid;
        }
        if(!empty($beginTime)){
            $sql = $sql." and payover.created_on>=:begin";
            $params[":begin"]=$beginTime;
        }
        if(!empty($endTime)){
            $sql = $sql." and payover.created_on<=:end";
            $params[":end"]=$endTime;
        }
        $sql .= " GROUP BY payover.application_id,payover.contract_id,payover.stock_out_code";

        $data = ContractManageEntity::getDb()->createCommand($sql,$params)->queryAll();
        return $data;
    }

    /**
     * 根据合同id获取合同信息
     *
     * @param string $erp_contract_id
     *
     * @return array|null|\yii\db\ActiveRecord
     */
    public static function getContractsCodeByLinkErpId($erp_contract_id = '')
    {
        if (empty($erp_contract_id)) {
            return [];
        }
        return ContractManageEntity::find()->select('contract_code,contract_id,myscrm_contract_business_type.business_type_name')
            ->leftJoin('myscrm_contract_business_type','myscrm_contract_business_type.business_type_id = myscrm_contract_manage.business_type_id')
            ->where(['erp_contract_id' => $erp_contract_id])->asArray()->all();
    }
}



